package fudan.se.project.repository.entity.conference.role;

import fudan.se.project.domain.User;
import fudan.se.project.domain.conference.Conference;
import fudan.se.project.domain.conference.post.Post;
import fudan.se.project.domain.conference.production.Paper;
import fudan.se.project.domain.conference.role.PcMember;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface PcMemberRepository extends CrudRepository<PcMember, Long> {

    PcMember findById(long id);

    @Query(value = "select " +
            "pcMember " +
            "from PcMember pcMember, Post post " +
            "where pcMember.user=:user " +
            "and post=:post " +
            "and " +
            "(pcMember in " +
            "(select distinct rvi.pcMember from post.paper.reviewInfos rvi )) "
    )
    PcMember findByUserAndPost(User user, Post post);

    PcMember findByUserAndConference(User user, Conference conference);

    @Query(value = "select " +
            "distinct pcMember " +
            "from PcMember pcMember " +
            "join pcMember.conference.papers paper " +
            "join pcMember.topics topic " +
            "join paper.writers writer " +
            "where paper=:paper " +
            "and " +
            "(topic in " +
            "(select distinct tpc from paper.topics tpc)) " +
            "and " +
            "(writer.email<>pcMember.user.email " +
            "and " +
            "writer.realname<>pcMember.user.realname) "
    )
    List<PcMember> findCandidateForPaperByTopicAndWriter(Paper paper);


    @Query(value = "select " +
            "distinct pcMember " +
            "from PcMember pcMember " +
            "where pcMember.conference=:conference "
    )
    List<PcMember> findAllCandidates(Conference conference);

}
